1 Imports System.Data.SqlClient
2 Public Class frmSection
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(ID) FROM Section")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Sub fillCombo()
26 Try
27 con = New SqlConnection(cs)
28 con.Open()
29 adp = New SqlDataAdapter()
30 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ClassName) FROM Class", con)
31 ds = New DataSet("ds")
32 adp.Fill(ds)
33 dtable = ds.Tables(0)
34 cmbClass.Items.Clear()
35 For Each drow As DataRow In dtable.Rows
36 cmbClass.Items.Add(drow(0).ToString())
37 Next
38 Catch ex As Exception
39 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
40 End Try
41 End Sub
42 Sub Reset()
43 cmbClass.SelectedIndex = -1
44 txtSearchBySection.Text = ""
45 txtSearchBySection.Text = ""
46 txtSection.Text = ""
47 txtSection.Focus()
48 btnSave.Enabled = True
49 btnUpdate.Enabled = False
50 btnDelete.Enabled = False
51 Getdata()
52 auto()
53 End Sub
54 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
55 Me.Close()
56 End Sub
57
58 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
59 If Len(Trim(txtSection.Text)) = 0 Then
60 MessageBox.Show("Please enter Section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
61 txtSection.Focus()
62 Exit Sub
63 End If
64 If Len(Trim(cmbClass.Text)) = 0 Then
65 MessageBox.Show("Please select Class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
66 cmbClass.Focus()
67 Exit Sub
68 End If
69 Try
70 con = New SqlConnection(cs)
71 con.Open()
72 Dim ct As String = "select SectionName,Class from Section where SectionName=@d1 and Class=@d2"
73 cmd = New SqlCommand(ct)
74 cmd.Connection = con
75 cmd.Parameters.AddWithValue("@d1", txtSection.Text)
76 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
77 rdr = cmd.ExecuteReader()
78
79 If rdr.Read() Then
80 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
81 txtSection.Text = ""
82 txtSection.Focus()
83 If (rdr IsNot Nothing) Then
84 rdr.Close()
85 End If
86 Return
87 End If
88
89 con = New SqlConnection(cs)
90 con.Open()
91
92 Dim cb As String = "insert into Section(ID,SectionName,Class) VALUES (" & txtID.Text & ",@d1,@d2)"
93 cmd = New SqlCommand(cb)
94 cmd.Connection = con
95 cmd.Parameters.AddWithValue("@d1", txtSection.Text)
96 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
97 cmd.ExecuteReader()
98 con.Close()
99 LogFunc(lblUser.Text, "added the new Section '" & txtSection.Text & "' having Class '" & cmbClass.Text & "'")
100 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
101 btnSave.Enabled = False
102 Getdata()
103 Catch ex As Exception
104 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
105 End Try
106 End Sub
107
108 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
109 Try
110 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
111 DeleteRecord()
112 End If
113 Catch ex As Exception
114 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
115 End Try
116 End Sub
117 Private Sub DeleteRecord()
118
119 Try
120 con = New SqlConnection(cs)
121 con.Open()
122 Dim cl As String = "select ID from Section,Student where Section.ID=Student.SectionID and ID=@d1"
123 cmd = New SqlCommand(cl)
124 cmd.Connection = con
125 cmd.Parameters.AddWithValue("@d1", txtID.Text)
126 rdr = cmd.ExecuteReader()
127 If rdr.Read Then
128 MessageBox.Show("Unable to delete..Already in use in Student Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
129 If Not rdr Is Nothing Then
130 rdr.Close()
131 End If
132 Exit Sub
133 End If
134 Dim RowsAffected As Integer = 0
135 con = New SqlConnection(cs)
136 con.Open()
137 Dim cq As String = "delete from Section where ID=@d1"
138 cmd = New SqlCommand(cq)
139 cmd.Connection = con
140 cmd.Parameters.AddWithValue("@d1", txtID.Text)
141 RowsAffected = cmd.ExecuteNonQuery()
142 If RowsAffected > 0 Then
143 LogFunc(lblUser.Text, "deleted the Section '" & txtSection.Text & "' having Class '" & cmbClass.Text & "'")
144 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
145 Getdata()
146 Reset()
147 Else
148 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
149 Reset()
150 End If
151 If con.State = ConnectionState.Open Then
152 con.Close()
153
154 End If
155 Catch ex As Exception
156 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
157 End Try
158 End Sub
159
160 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
161 Try
162 If Len(Trim(txtSection.Text)) = 0 Then
163 MessageBox.Show("Please enter Section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
164 txtSection.Focus()
165 Exit Sub
166 End If
167 If Len(Trim(cmbClass.Text)) = 0 Then
168 MessageBox.Show("Please select Class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
169 cmbClass.Focus()
170 Exit Sub
171 End If
172 con = New SqlConnection(cs)
173 con.Open()
174 Dim cb As String = "update Section set SectionName=@d1,Class=@d2 where ID=@d3"
175 cmd = New SqlCommand(cb)
176 cmd.Connection = con
177 cmd.Parameters.AddWithValue("@d1", txtSection.Text)
178 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
179 cmd.Parameters.AddWithValue("@d3", txtID.Text)
180 cmd.ExecuteReader()
181 con.Close()
182 LogFunc(lblUser.Text, "updated the Section '" & txtSection.Text & "' having Class '" & cmbClass.Text & "'")
183 MessageBox.Show("Successfully updated", "Section Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
184 btnUpdate.Enabled = False
185 Getdata()
186 Catch ex As Exception
187 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
188 End Try
189 End Sub
190 Public Sub Getdata()
191 Try
192 con = New SqlConnection(cs)
193 con.Open()
194 cmd = New SqlCommand("SELECT RTRIM(ID), RTRIM(SectionName), RTRIM(Class) from Section order by SectionName", con)
195 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
196 dgw.Rows.Clear()
197 While (rdr.Read() = True)
198 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
199 End While
200 con.Close()
201 Catch ex As Exception
202 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
203 End Try
204 End Sub
205 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
206 Reset()
207 End Sub
208
209
210 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
211 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
212 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
213 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
214 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
215 End If
216 Dim b As Brush = SystemBrushes.ControlText
217 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
218
219 End Sub
220
221 Private Sub frmCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
222 Getdata()
223 fillCombo()
224 End Sub
225
226 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
227 Try
228 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
229 txtSection.Text = dr.Cells(1).Value.ToString()
230 txtID.Text = dr.Cells(0).Value.ToString()
231 cmbClass.Text = dr.Cells(2).Value.ToString()
232 btnUpdate.Enabled = True
233 btnDelete.Enabled = True
234 btnSave.Enabled = False
235 Catch ex As Exception
236 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
237 End Try
238 End Sub
239
240 Private Sub txtSearchByCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchBySection.TextChanged
241 Try
242 con = New SqlConnection(cs)
243 con.Open()
244 cmd = New SqlCommand("SELECT RTRIM(ID), RTRIM(SectionName), RTRIM(Class) from Section where SectionName like '" & txtSearchBySection.Text & "%' order by SectionName", con)
245 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
246 dgw.Rows.Clear()
247 While (rdr.Read() = True)
248 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
249 End While
250 con.Close()
251 Catch ex As Exception
252 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
253 End Try
254 End Sub
255
256
257 Private Sub txtSearchByClass_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByClass.TextChanged
258 Try
259 con = New SqlConnection(cs)
260 con.Open()
261 cmd = New SqlCommand("SELECT RTRIM(ID), RTRIM(SectionName), RTRIM(Class) from Section where Class like '" & txtSearchByClass.Text & "%' order by SectionName", con)
262 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
263 dgw.Rows.Clear()
264 While (rdr.Read() = True)
265 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
266 End While
267 con.Close()
268 Catch ex As Exception
269 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
270 End Try
271 End Sub
272 End Class
